package com.example.tenmins;


import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class Database {

	private static String DB_NAME = "alarm";
	private static int DB_VERSION = 10;

	private final Context context;
	
	private DatabaseHelper mDbHelper;
    private static SQLiteDatabase db;

	public static class DatabaseHelper extends SQLiteOpenHelper {
		
		DatabaseHelper (Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			String sql = "CREATE TABLE list(hour INT, minute INT, am_pm TEXT,"
					+ " mon INT, tue INT, wed INT, thu INT, fri INT, sat INT, sun INT,"
					+ " game TEXT, sound TEXT, volume INT, id INT)";
			db.execSQL(sql);
			
			String sql_create_music = "CREATE TABLE musicList(musicID TEXT, id INT)";
			db.execSQL(sql_create_music);
			
			String sql2 = "CREATE TABLE last_id(id INT)";
			String sql3 = "INSERT INTO last_id(id) VALUES (1);";
			db.execSQL(sql2);
			db.execSQL(sql3);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS list; ");
			db.execSQL("DROP TABLE IF EXISTS last_id;");
			db.execSQL("DROP TABLE IF EXISTS musicList;");
			onCreate(db);
		}

	}
	
	public Database(Context context) {
        this.context = context;
    }
   
    public Database open() throws SQLException {
        mDbHelper = new DatabaseHelper(context);
        db = mDbHelper.getWritableDatabase();
        return this;
    }
   
    public void close() {
        mDbHelper.close();
    }

	public void addAlarmList(int hour, int minute, String am_pm, int mon,
			int tue, int wed, int thu, int fri, int sat, int sun, String game,
			String sound, int volume) {
		
		int id = getLastId();
		id++;

		db.execSQL("INSERT INTO list(hour, minute, am_pm, mon, tue, wed, thu, fri, sat, sun, game, sound, volume, id)" +
				" VALUES (" + hour + "," + minute + ",'"
				+ am_pm + "'" + "," + mon + "," + tue + "," + wed + "," + thu
				+ "," + fri + "," + sat + "," + sun + ",'" + game + "'" + ",'"
				+ sound + "'," + volume + ","+id+");");
		
		db.execSQL("UPDATE last_id SET id = "+id+" WHERE id="+(id-1)+";");
		
	}
	
	public void addMusicList(String musicID) {
		
		int id = getLastId();
		id++;

		db.execSQL("INSERT INTO musicList(musicID, id)" +
				" VALUES ('" + musicID + "'," + id + ");");	
	}

	public void deleteAlarmList(int id) {

		db.execSQL("DELETE FROM list WHERE id ="+id+";");
	}
	
	public void updateAlarmList(int hour, int minute, String am_pm, int mon,
			int tue, int wed, int thu, int fri, int sat, int sun, String game,
			String sound, int volume, int id) {

		db.execSQL("UPDATE list SET hour ="+hour+", minute ="+minute+", am_pm ='"+am_pm+"', mon ="+mon+
				", tue="+tue+", wed="+wed+", thu="+thu+", fri="+fri+", sat="+sat+", sun="+sun+", game='"+game+
				"', sound='"+sound+"', volume="+volume+" WHERE id ="+id+";");
		
	}
	
	public void updateMusicList(String musicID, int id) {

		db.execSQL("UPDATE musicList SET musicID ='"+musicID+"', id ="+id+";");
		
	}

	public Cursor getAlarmList() {
		Cursor c = db.rawQuery("SELECT * FROM list;", null);

		if (c != null && c.getCount() != 0)
			c.moveToFirst();

		return c;
	}
	
	public int getLastId(){
		Cursor c= db.rawQuery("SELECT id FROM last_id;",null);
		c.moveToFirst();
		return c.getInt(0);
	}
	
	public int getGame(int id){
		Cursor c= db.rawQuery("SELECT game FROM list WHERE id ="+id+";", null);
		c.moveToFirst();		
		String game = c.getString(0);
		
		if(game.equals("없음"))
			return 0;
		else if(game.equals("문자입력"))
			return 1;
		else  // 구구??
			return 2;
		
	}
	
	public String getMusicID(int id){
		Cursor c= db.rawQuery("SELECT musicID FROM musicList WHERE id ='"+id+"';", null);
		c.moveToFirst();			
		String musicID = c.getString(0);
		return musicID;
	}
	
	public int getHour(int id){
		Log.e("tag", "id:"+id);
		Cursor c= db.rawQuery("SELECT hour FROM list WHERE id ="+id+"", null);
		c.moveToFirst();		
		return c.getInt(0);
		
	}
	
	public int getMin(int id){
		Log.e("tag", "id:"+id);
		Cursor c= db.rawQuery("SELECT minute FROM list WHERE id ="+id+"", null);
		c.moveToFirst();		
		return c.getInt(0);
		
	}
	
	public String getAMPM(int id)
	{
		Log.e("tag", "id:"+id);
		Cursor c= db.rawQuery("SELECT am_pm FROM list WHERE id ="+id+"", null);
		c.moveToFirst();		
		return c.getString(0);
	}
	
	public boolean isCheckedMon(int id){
		Cursor c = db.rawQuery("SELECT mon FROM list WHERE id="+id+";", null);
		c.moveToFirst();
		if(c.getInt(0)==0)
			return false;
		else
			return true;
	}
	
	public boolean isCheckedTue(int id){
		Cursor c = db.rawQuery("SELECT tue FROM list WHERE id="+id+";", null);
		c.moveToFirst();
		if(c.getInt(0)==0)
			return false;
		else
			return true;
	}
	public boolean isCheckedWed(int id){
		Cursor c = db.rawQuery("SELECT wed FROM list WHERE id="+id+";", null);
		c.moveToFirst();
		if(c.getInt(0)==0)
			return false;
		else
			return true;
	}
	
	public boolean isCheckedThu(int id){
		Cursor c = db.rawQuery("SELECT thu FROM list WHERE id="+id+";", null);
		c.moveToFirst();
		if(c.getInt(0)==0)
			return false;
		else
			return true;
	}
	public boolean isCheckedFri(int id){
		Cursor c = db.rawQuery("SELECT fri FROM list WHERE id="+id+";", null);
		c.moveToFirst();
		if(c.getInt(0)==0)
			return false;
		else
			return true;
	}
	public boolean isCheckedSat(int id){
		Cursor c = db.rawQuery("SELECT sat FROM list WHERE id="+id+";", null);
		c.moveToFirst();
		if(c.getInt(0)==0)
			return false;
		else
			return true;
	}
	
	public boolean isCheckedSun(int id){
		Cursor c = db.rawQuery("SELECT sun FROM list WHERE id="+id+";", null);
		c.moveToFirst();
		if(c.getInt(0)==0)
			return false;
		else
			return true;
	}
	
	public int getVolume(int id){
		Cursor c= db.rawQuery("SELECT volume FROM list WHERE id ="+id+"", null);
		c.moveToFirst();		
		int volume = c.getInt(0);	
		
		return volume;
	}
	
	public String getMusicTitle(int id){
		Cursor c= db.rawQuery("SELECT sound FROM list WHERE id ="+id+"", null);
		c.moveToFirst();		
		String title = c.getString(0);	
		
		return title;
	}
}
